Attribute VB_Name = "NewSummarySheet_Creation"


'' ==============================================================================================
'' Procedure which will create a New sheet from the information Exported from Siebel
'' BE CARE Export ESCALATION SR ONLY
'' In Siebel Export the Data in a .CSV file, than apply this Procedure
'' ==============================================================================================
Sub CreateNewSheet_From_Siebel_EscalationSR_Export()
  Dim MyListIn(14) As String
  Dim MyListOut(14) As String
  Dim n, Nb, nbl As Integer
  Dim Column_Escalated_SR As String
  Dim Name As String
  Dim tmp As String
  Dim tmp_ar() As String
  Dim out As String
    
    '' Initialisation
    Column_Escalated_SR = "J"
    
    MyListIn(0) = "SR #"
    MyListIn(1) = "Associated Account"
    MyListIn(2) = "*Summary*"
    MyListIn(3) = "Created Date"
    MyListIn(4) = "*Last Name*"
    MyListIn(5) = "*Product Category*"
    MyListIn(6) = "*Severity*"
    MyListIn(7) = "Reference #"
    MyListIn(8) = ""
    MyListIn(9) = "Invoice Number"
    MyListIn(10) = ""
    MyListIn(11) = ""
    MyListIn(12) = "*Status*"
    MyListIn(13) = ""
    MyListIn(14) = ""
          
    '' Column to copy (This column countains the needed Information
    Range("D:D,M:M,Z:Z,AA:AA,AB:AB,AH:AH,AL:AL,AN:AN,AR:AR,AS:AS").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("AA1").Select
    ActiveSheet.Paste
    
    '' Working on the Data
    '' Will Search : MyListIn and rename it by MyListOut
    n = 1
    For Each Name In MyListIn
        Range("A1").Select
        Cells.Find(What:=Name, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.EntireColumn.Select
        Selection.Cut
        Columns(n).Select
        ActiveSheet.Paste
        n = n + 1
    Next
    
    '' Extract Technical SR
    out = ""
    Nb = 0
    nbl = 0
     For j = 2 To Cells(65536, 1).End(xlUp).Row
        tmp = Cells(j, Column_Escalated_SR).Value
        If tmp <> Empty Then
            nbl = nbl + 1
        End If
        tmp_ar = Split(tmp, ",")
        For i = 0 To UBound(tmp_ar)
            If Nb = 0 Then
                out = tmp_ar(i)
            Else
                out = out & " or " & tmp_ar(i)
            End If
            Nb = Nb + 1
        Next i
    Next
    Range("J" & nbl + 3).Select
    ActiveCell.FormulaR1C1 = out
    
End Sub

'' ==============================================================================================
'' Procedure which will create a New sheet from the information Exported from Siebel
'' BE CARE Export SR ONLY
'' In Siebel Export the Data in a .CSV file, than apply this Procedure
'' ==============================================================================================
Sub CreateNewSheet_From_Siebel_SR_Export()

  Dim MyListIn(14) As String
  Dim MyListOut(14) As String
  Dim n, Nb, nbl As Integer
  Dim Column_Escalated_SR As String
  Dim Name As Variant
  Dim tmp As String
  Dim tmp_ar() As String
  Dim out As String
    
    '' Initialisation
    MyListIn(0) = "SR #"                    ' Z x
    MyListIn(1) = "Associated Account"      ' D x
    MyListIn(2) = "*Summary*"               ' AB x
    MyListIn(3) = "Created Date"            ' AO
    MyListIn(4) = "*Last Name*"             ' AI
    MyListIn(5) = "*Product Category*"      ' AM
    MyListIn(6) = "*Severity*"              ' AS
    MyListIn(7) = "Reference #"             ' AA x
    MyListIn(8) = ""
    MyListIn(9) = "Invoice Number"          ' M x
    MyListIn(10) = ""
    MyListIn(11) = ""
    MyListIn(12) = "*Status*"               ' W
    MyListIn(13) = ""
    MyListIn(14) = ""
   
    MyListOut(0) = "SR"
    MyListOut(1) = "End Customer"
    MyListOut(2) = "Issue Summary"
    MyListOut(3) = "SR Creation Date"
    MyListOut(4) = "Case Owner"
    MyListOut(5) = "Product"
    MyListOut(6) = "SR:Severity"
    MyListOut(7) = "R&D crms"
    MyListOut(8) = "Reason of Escalation"
    MyListOut(9) = "Escalated SR"
    MyListOut(10) = "Critcality"
    MyListOut(11) = "BP / RSC Comments"
    MyListOut(12) = "Next Action"
    MyListOut(13) = "Action Point"
    MyListOut(14) = "Closed"
    
    Column_Escalated_SR = "J"
    
    '' Column to copy (This column countains the needed Information
    
    Range("D:D,M:M,Z:Z,BD:BD,V:V,X:X,AA:AA,AB:AB,AI:AI,AO:AO,AM:AM,AR:AR,AT:AT,AS:AS,AW:AW,W:W,AY:AY").Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1").Select
    ActiveSheet.Paste
    

        
    MiseEnForme_Siebel_Export
        
End Sub






' ====================================
' Sheet used for Specific functions
' ====================================
Public Function MiseEnForme_Siebel_Export()

    Columns("C:C").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 34.5
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Selection.Rows.AutoFit
    Columns("D:D").Select
    Selection.NumberFormat = "d/m/yy;@"
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 15.17
    Columns("F:F").Select
    Selection.ColumnWidth = 13.83
    Columns("G:G").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").Select
    Selection.ColumnWidth = 14.33
    Columns("M:M").ColumnWidth = 17.17
    Range("M4").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("G1,K1").Select
    Range("K1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("C7").Select
    Rows("1:1").RowHeight = 53.25
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    Columns("L:L").ColumnWidth = 17.5
    Columns("L:L").ColumnWidth = 18.83
    Columns("M:M").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("N:N").ColumnWidth = 16
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("F20").Select
    Columns("B:B").ColumnWidth = 15.5
    Cells.Select
    With Selection
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

 Columns("I:I").ColumnWidth = 17.67
    Range("G1,K1").Select
    Range("K1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Range("A1:O166").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$O$166"), , xlYes).Name = _
        "Tableau5"
    Range("Tableau5[#All]").Select
    ActiveSheet.ListObjects("Tableau5").TableStyle = "TableStyleLight19"
    Range("Tableau5[#Headers]").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("Tableau5[#Headers]").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("A2").Select
    
End Function
